package com.liuxl.db.tool;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.github.pagehelper.Page;
import com.google.common.collect.Lists;
import com.liuxl.db.JdbcUtils;

public class OracleDatabaseTool extends AbstractDatabaseTool {

	@Override
	public List<String> getTableNames(DataSource dataSource) throws SQLException {
		List<String> tableNames = Lists.newArrayList();
		String sql = "select table_name from user_tables";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql);
		for (Map<String, Object> data : datas) {
			String tableName = data.get("TABLE_NAME") == null ? "" : data.get("TABLE_NAME").toString();
			tableNames.add(tableName);
		}
		return tableNames;
	}

	@Override
	public List<String> getColumnNames(DataSource dataSource, String tableName) throws SQLException {
		List<String> columnNames = Lists.newArrayList();
		String sql = "select column_name from user_tab_columns where table_name = ?";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql,tableName);
		for (Map<String, Object> data : datas) {
			String columnName = data.get("COLUMN_NAME") == null ? "" : data.get("COLUMN_NAME").toString();
			columnNames.add(columnName);
		}
		return columnNames;
	}

	@Override
	public String getPageSql(String sql) {
		StringBuilder sqlBuilder = new StringBuilder(sql.length() + 120);
        sqlBuilder.append("SELECT * FROM ( ");
        sqlBuilder.append(" SELECT TMP_PAGE.*, ROWNUM ROW_ID FROM ( ");
        sqlBuilder.append(sql);
        sqlBuilder.append(" ) TMP_PAGE)");
        sqlBuilder.append(" WHERE ROW_ID <= ? AND ROW_ID > ?");
        return sqlBuilder.toString();
	}

	@Override
	public void addPageParameter(Page<Map<String,Object>> page, List<Object> parameters) {
		parameters.add(page.getEndRow());
		parameters.add(page.getStartRow());
	}

	@Override
	public boolean existsTableName(DataSource dataSource, String tableName) throws SQLException {
		String sql = "select count(1) from user_tables where table_name = ?";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql,tableName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}

	@Override
	public boolean existsColumnName(DataSource dataSource, String tableName, String columnName) throws SQLException {
		String sql = "select count(0) from user_tab_columns where table_name = ? and column_name = ?";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql,tableName,columnName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}

}
